-- Table: project_role

-- DROP TABLE project_role;

CREATE TABLE project_role
(
  id serial NOT NULL,
  "name" character varying(255),
  code character varying(20),
  description character varying(255),
  created timestamp without time zone DEFAULT now(),
  creator integer,
  updated timestamp without time zone DEFAULT now(),
  updator integer,
  deleted boolean DEFAULT false,
  deletion_time timestamp without time zone,
  CONSTRAINT project_role_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE project_role OWNER TO postgres;

--procedures
CREATE OR REPLACE FUNCTION sp_project_role_insert(
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying(255) DEFAULT null,
	_creator	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO project_role(name,code,description,creator,updator,deletion_time)
	VALUES(_name,_code,_description,_creator,_creator,null);
RETURN currval('project_role_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_role_select(_id	integer)
RETURNS SETOF project_role
AS $$
BEGIN
	RETURN QUERY SELECT * FROM project_role WHERE id = _id;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_role_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying(255) DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS SETOF project_role
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM project_role WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_role_count (
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying(255) DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM project_role WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_project_role_delete(_id integer)
	RETURNS INT as
$$
BEGIN
		DELETE FROM project_role
 		WHERE "id" = _id;
		RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_role_update 
(	_id integer,
	_name character varying(255),
	_code character varying(20),
	_description character varying(255),
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE project_role 
	SET 		name = _name,
		code = _code,
		description = _description,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
